package bd;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import Codigo.*;

public class DBUtils {
	
	private Statement statement = null;
	private Connection conn = null;
	private static DBUtils _instance = null;
	
	private DBUtils(){}
	
	public static DBUtils getInstance()
	{
		if( _instance == null )
		{
			_instance = new DBUtils();
		}
		
		return _instance;
	}
	
	public Statement getStatement()
	{
		return statement;
	}
	
	public boolean openConnection(String dbName, DBStrategy strategy)
	{
		try
		{
			String database = "";
			if( strategy == DBStrategy.WINDOWS )
			{
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	            database = 
	              "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+ dbName +";";
			}
			else if( strategy == DBStrategy.MAC )
			{
				Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();
	            database = "jdbc:access:////"+ dbName;
			}
            conn = DriverManager.getConnection(database);
            statement = conn.createStatement();
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
			return false;
		}
		
		return true;
	}
	
	public void createTable(String tableName, String[] collumns, String[] types) throws SQLException
	{
		Statement s = getStatement();
		
		String query = tableName + " (";
		
		for( int i = 0; i < collumns.length; i++ )
		{
			query += collumns[i] + " " + types[i];
		}
		
		query += ")";
		
		s.execute("CREATE TABLE " + query);
	}
	
	public void runQuery(String query) throws SQLException
	{
		Statement s = getStatement();
		s.execute(query);
	}
	
	public void setMessage(int msgNumber, String login) throws SQLException
	{
		runQuery("SELECT Now()");
        
    	String date = "";
    	ResultSet rs = getStatement().getResultSet();
        while((rs!=null) && (rs.next()))
        {
        	date = rs.getString(1);
        }
        
		runQuery("INSERT INTO Registros (msg_number, login_name, event_date) VALUES ("+ msgNumber +", '"+ login +"', '"+date+"')");
	}
	
	public Usuario checkLogin(String login) throws SQLException
	{
		runQuery("SELECT * FROM Usuarios WHERE login_name='"+ login +"'");
		
		ResultSet rs = getStatement().getResultSet();
        while((rs!=null) && (rs.next()))
        {
        	String login_name = rs.getString(1);
        	if( !login_name.isEmpty() )
        	{
        		return new Usuario(login_name,rs.getString(2), rs.getInt(6), rs.getBoolean(5), rs.getInt(4), rs.getString(3));
        	}
        	
        	return null;
        }
        
        return null;
	}
	
	public void closeConnection() throws SQLException
	{
		Statement s = getStatement();
		s.close();
		conn.close();
	}

	public static void main(String[] args)
    {
        try
        {
        	
        	DBUtils dbUtils = new DBUtils();

        	dbUtils.openConnection("/Users/diguinho_r1/Documents/workspace/t3seguranca/DBSeguranca.mdb", DBStrategy.MAC);
            
        	//dbUtils.runQuery("DROP TABLE Registros");
        	//dbUtils.runQuery("DROP TABLE Usuarios");
        	//dbUtils.runQuery("DROP TABLE Grupos");
        	
        	/*dbUtils.createTable("Usuarios", 
            		new String[] {"login_name", "hash_word", "description", "group", "blocked", "access"} , 
            		new String[] {"CHAR(255) PRIMARY KEY,", "CHAR(255),", "CHAR(255),", "Integer,", "BIT,", "Integer"});
        	
        	dbUtils.createTable("Registros", 
            		new String[] {"msg_number", "login_name", "event_date"} , 
            		new String[] {"Integer,", "CHAR(255),", "DATE"});
        	
        	dbUtils.createTable("Grupos", 
            		new String[] {"group_name"} , 
            		new String[] {"CHAR(255) Primary key"});
        	
        	
        	dbUtils.runQuery("Alter Table Grupos Add Column GID int primary key");*/
        	dbUtils.runQuery("INSERT INTO Usuarios (login_name, hash_word, description, group, blocked, access) " +
        									"VALUES ('rodrigo', 'abc', 'teste', 1, 0, 1)");
        	dbUtils.runQuery("SELECT * FROM Registros");
            
        	
        	ResultSet rs = dbUtils.getStatement().getResultSet();
            while((rs!=null) && (rs.next()))
            {
                System.out.println(rs.getInt(1) + " " + rs.getString(2)+ " " +rs.getString(3));
            }
            // close and cleanup
            dbUtils.closeConnection();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
}
